# core/database/connection.py
import os
import sqlite3
from contextlib import contextmanager

class DatabaseManager:
    def __init__(self):
        self._path = None
        self._initialized = False

    def initialize(self, plugin_dir: str):
        """必须在插件初始化时调用"""
        if self._initialized:
            return

        self._path = os.path.join(plugin_dir, "example.db")
        print(f"Database path: {self._path}")

        ddl = """
        CREATE TABLE IF NOT EXISTS tag_groups (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                group_name TEXT NOT NULL,
                name TEXT NOT NULL,
                UNIQUE(group_name, name)
              );

              CREATE TABLE IF NOT EXISTS tags (
                tag_group_id INTEGER NOT NULL,
                tag TEXT NOT NULL,
                tag_des TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (tag_group_id, tag),
                FOREIGN KEY (tag_group_id) REFERENCES tag_groups(id)
                    ON DELETE CASCADE  -- 可选：分组删除时，自动删除该分组下所有标签（避免孤行）
              );

              CREATE TABLE IF NOT EXISTS prompt_items (
                id TEXT PRIMARY KEY DEFAULT (hex(randomblob(16))),
                class_type TEXT NOT NULL,
                name TEXT ,
                customize_name TEXT ,
                image TEXT,
                tags TEXT,
                description TEXT,
                rating REAL DEFAULT 0.0 CHECK(rating BETWEEN 0 AND 10),
                favorite BOOLEAN DEFAULT 0,
                disliked BOOLEAN DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
              );
              DROP INDEX IF EXISTS idx_class_created_id;
              CREATE INDEX IF NOT EXISTS idx_class_updated_id ON prompt_items (class_type, updated_at, id);
              CREATE INDEX IF NOT EXISTS idx_prompt_items_rating ON prompt_items (rating);
              CREATE INDEX IF NOT EXISTS idx_prompt_items_created ON prompt_items (updated_at);
              CREATE INDEX IF NOT EXISTS idx_prompt_items_class_type ON prompt_items (class_type);

              CREATE TABLE IF NOT EXISTS character (
                name TEXT PRIMARY KEY,
                cn_name TEXT,
                tag TEXT NOT NULL,
                description TEXT,
                rating REAL DEFAULT 0.0 CHECK(rating BETWEEN 0 AND 10),
                favorite BOOLEAN DEFAULT 0,
                disliked BOOLEAN DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
              );
              CREATE INDEX IF NOT EXISTS idx_character_rating ON character(rating);

              CREATE TABLE IF NOT EXISTS random_tag_configs (
                  id INTEGER PRIMARY KEY AUTOINCREMENT,
                  name TEXT NOT NULL,
                  config_json TEXT NOT NULL,
                  create_time INTEGER NOT NULL
              );
              CREATE TABLE IF NOT EXISTS translate (
                en TEXT PRIMARY KEY,
                cn TEXT NOT NULL,
                hit_count INTEGER DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
              );
              CREATE INDEX IF NOT EXISTS idx_translate_en ON translate(en);
                        """
        with self.get_connection() as conn:
            conn.executescript(ddl)
            conn.commit()

        self._initialized = True

    @contextmanager
    def get_connection(self):
        """线程安全的连接获取"""
        conn = sqlite3.connect(self._path, check_same_thread=False)
        conn.row_factory = sqlite3.Row
        try:
            yield conn
            conn.commit()
        finally:
            conn.close()

# 全局单例
db_manager = DatabaseManager()